Attribute VB_Name = "modDataFix"
Option Explicit
'==================================================================================
'Copyright (c) 2003, Andrew Jeremy Gargan (ajg@osite.co.za), Terence Leslie Van Zyl (tvz@osite.co.za)
'
'All rights reserved.
'
'Redistribution and use in source and binary forms, with or without modification,
'are permitted provided that the following conditions are met:
'
'Redistributions of source code must retain the above copyright notice, this list
'of conditions and the following disclaimer.
'
'Redistributions in binary form must reproduce the above copyright notice, this
'list of conditions and the following disclaimer in the documentation and/or other
'materials provided with the distribution.
'
'Neither the name of the <ORGANIZATION> nor the names of its contributors may be
'used to endorse or promote products derived from this software without specific
'prior written permission.
'
'THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
'ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
'WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
'IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
'INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
'BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, 
'DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
'LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE
'OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
'OF THE POSSIBILITY OF SUCH DAMAGE.
'===================================================================================
Public Sub fix_images(aFormat As PicFormat32)
    Dim lvarSplitStr() As String
    Dim lvarSQL As String
    Dim lvarRS As DAO.Recordset
    Dim lvarPath As String
    Dim lvarField As String
    Dim lvarCounter As Long
    Dim lvarFSO As New FileSystemObject
    
    'loop through ASSESSMENTS
    lvarSQL = "SELECT * FROM ASSESSMENT"
    Set lvarRS = gvarDatabase.OpenRecordset(lvarSQL, dbOpenForwardOnly)
    Do While Not lvarRS.EOF
        lvarSQL = "UPDATE ASSESSMENT SET "
        For lvarCounter = 1 To 12
            lvarPath = "blank.jpg"
            lvarField = "ASS_PHOTO_" & Trim(Str(lvarCounter))
            If Not IsNull(lvarRS(lvarField)) Then
                If lvarRS(lvarField) <> "" Then
                    If lvarRS(lvarField) Like "*\*" Then
                        'Remove "C:\...\photos\"
                        lvarSplitStr = Split(lvarRS(lvarField), "\")
                        lvarPath = lvarSplitStr(UBound(lvarSplitStr))
                    Else
                        lvarPath = lvarRS(lvarField)
                    End If
                End If
            End If
            lvarSQL = lvarSQL & lvarField & " = '" & lvarPath & "',"
        Next
        
        'Damage picture
        lvarField = "ASS_DAMAGE_PICTURE"
        lvarPath = lvarRS("ASS_KEY") & ".jpg"
        If lvarFSO.FileExists(VB.App.Path & "\" & lvarRS("ASS_KEY") & ".bmp") Then
            
            aFormat.SaveBmpToJpeg VB.App.Path & "\" & lvarRS("ASS_KEY") & ".bmp", gvarDamagePath & lvarPath, 100
            lvarFSO.DeleteFile VB.App.Path & "\" & lvarRS("ASS_KEY") & ".bmp"
        End If
        
        lvarSQL = lvarSQL & lvarField & " = '" & lvarPath & "'"
        
        lvarSQL = lvarSQL & " WHERE ASS_KEY=" & lvarRS("ASS_KEY")
        gvarDatabase.Execute lvarSQL, dbFailOnError
        'move along
        lvarRS.MoveNext
    Loop
End Sub

Public Sub fix_data()
    Dim lvarTable As TableDef
    Dim lvarField As Field
    Dim lvarRS1 As DAO.Recordset
    Dim lvarQuery1 As String
    Dim lvarQuery2 As String
    Dim lvarVal As String
    
    On Error GoTo errorhandler
    DBEngine.BeginTrans
    
    'Add Extra Company Fields
    Set lvarTable = gvarDatabase.TableDefs("USER")
    Set lvarField = lvarTable.CreateField("USR_ADDRESS_1", dbText, 50)
    lvarTable.Fields.Append lvarField
    Set lvarField = lvarTable.CreateField("USR_ADDRESS_2", dbText, 50)
    lvarTable.Fields.Append lvarField
    Set lvarField = lvarTable.CreateField("USR_ADDRESS_3", dbText, 50)
    lvarTable.Fields.Append lvarField
    Set lvarField = lvarTable.CreateField("USR_PCODE", dbText, 4)
    lvarTable.Fields.Append lvarField
    
    'Fix Assessment fields
     gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN TEMP TEXT(20);")
     gvarDatabase.Execute ("UPDATE DISTINCTROW ASSESSMENT SET TEMP=Str(ASS_VAT_NO);")
     gvarDatabase.Execute ("ALTER TABLE ASSESSMENT DROP COLUMN ASS_VAT_NO;")
     gvarDatabase.TableDefs("ASSESSMENT").Fields("TEMP").Name = "ASS_VAT_NO"
     
     gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN ASS_AUTHORISED BIT;")
     gvarDatabase.Execute ("UPDATE DISTINCTROW ASSESSMENT SET ASS_AUTHORISED = ASS_AUTHORIZE;")
     gvarDatabase.Execute ("ALTER TABLE ASSESSMENT DROP COLUMN ASS_AUTHORIZE;")
          
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_VAT_NO='UNKNOWN' WHERE ASS_VAT_NO IS NULL;")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_INSURANCE='UNKNOWN' WHERE ASS_INSURANCE IS NULL OR ASS_INSURANCE='';")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_INSURANCE_BRANCH='UNKNOWN' WHERE ASS_INSURANCE_BRANCH IS NULL OR ASS_INSURANCE_BRANCH='';")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_INSURANCE_CLARK='UNKNOWN' WHERE ASS_INSURANCE_CLARK IS NULL OR ASS_INSURANCE_CLARK='';")
     
     'Additions to ASSESSMENT Table
     Set lvarTable = gvarDatabase.TableDefs("ASSESSMENT")
     Set lvarField = lvarTable.CreateField("ASS_PHOTO_9", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_PHOTO_10", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_PHOTO_11", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_PHOTO_12", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_PAID", dbBoolean)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_SAVINGS", dbCurrency)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_ADDITIONALS", dbCurrency)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_PAID_DATE", dbDate)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_DATE_AUTHORISED", dbDate)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_DATE_AUTHORISED_SENT", dbBoolean)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_REPORTED", dbBoolean)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_RE_ASSESSMENT", dbBoolean)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("ASS_VEHICLE_STATE", dbLong)
     lvarTable.Fields.Append lvarField
     
     'Set fields to values
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_VEHICLE_STATE=0;")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_PAID=true, ASS_PAID_DATE=ASS_DATE_ASSESSED;")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_REPORTED=true, ASS_PAID_DATE=ASS_DATE_ASSESSED;")
     gvarDatabase.Execute ("UPDATE ASSESSMENT SET ASS_AUTHORISED=true, ASS_DATE_AUTHORISED_SENT=true, ASS_DATE_AUTHORISED=ASS_DATE_ASSESSED;")
          
     'Creating Configuration Table
     Set lvarTable = gvarDatabase.CreateTableDef("CONFIG")
     Set lvarField = lvarTable.CreateField("CON_DATA_DIR", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("CON_IMAGE_DIR", dbText, 100)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("CON_EMAIL_ADDRESS", dbText, 50)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("CON_EMAIL_USER", dbText, 50)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("CON_EMAIL_PASSWORD", dbText, 50)
     lvarTable.Fields.Append lvarField
     gvarDatabase.TableDefs.Append lvarTable
     gvarDatabase.Execute ("ALTER TABLE CONFIG ADD COLUMN CON_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    
     'Reminders Table
     Set lvarTable = gvarDatabase.CreateTableDef("REMINDER")
     Set lvarField = lvarTable.CreateField("ASS_KEY", dbLong)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_CREATED", dbDate)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_NOTE", dbMemo)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_NOTIFY_FROM", dbDate)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_SEQUENCE", dbLong)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_COMPLETED", dbBoolean)
     lvarTable.Fields.Append lvarField
     Set lvarField = lvarTable.CreateField("NOT_COMPLETED_DATE", dbDate, 50)
     lvarTable.Fields.Append lvarField
     gvarDatabase.TableDefs.Append lvarTable
     gvarDatabase.Execute ("ALTER TABLE REMINDER ADD COLUMN NOT_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    
    'Add Extra Assessment Fields
    'Get Data from outside
    gvarDatabase.Execute ("SELECT DISTINCT ASS_ASSESSOR AS ASO_NAME, ASS_ASSESSOR_TEL AS ASO_TEL INTO ASSESOR FROM ASSESSMENT;")
    gvarDatabase.Execute ("SELECT DISTINCT ASS_INSURANCE_CLARK AS CLR_NAME,ASS_INSURANCE_TEL AS CLR_TEL INTO CLARK FROM ASSESSMENT;")
    gvarDatabase.Execute ("SELECT DISTINCT ASS_VAT_NO AS INS_VAT_NO,ASS_INSURANCE AS INS_NAME INTO INSURANCE FROM ASSESSMENT;")
    gvarDatabase.Execute ("SELECT DISTINCT ASS_INSURANCE_BRANCH AS BRA_NAME INTO BRANCH FROM ASSESSMENT;")
    
    gvarDatabase.Execute ("ALTER TABLE INSURANCE ADD COLUMN INS_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    gvarDatabase.Execute ("ALTER TABLE INSURANCE ADD COLUMN INS_REG TEXT(50)")
    gvarDatabase.Execute ("ALTER TABLE INSURANCE ADD COLUMN INS_TEL TEXT(20)")
    gvarDatabase.Execute ("ALTER TABLE INSURANCE ADD COLUMN INS_FAX TEXT(20)")
    
    gvarDatabase.Execute ("ALTER TABLE BRANCH ADD COLUMN BRA_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    gvarDatabase.Execute ("ALTER TABLE BRANCH ADD COLUMN BRA_FAX TEXT(20);")
    gvarDatabase.Execute ("ALTER TABLE BRANCH ADD COLUMN BRA_FULL_NAME TEXT(50);")
    gvarDatabase.Execute ("ALTER TABLE BRANCH ADD COLUMN BRA_TEL TEXT(20);")
    gvarDatabase.Execute ("ALTER TABLE BRANCH ADD COLUMN BRA_VAT_NO TEXT(50)")
    
    gvarDatabase.Execute ("ALTER TABLE ASSESOR ADD COLUMN ASO_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    gvarDatabase.Execute ("ALTER TABLE ASSESOR ADD COLUMN ASO_CELL TEXT(20);")
    
    gvarDatabase.Execute ("ALTER TABLE CLARK ADD COLUMN CLR_KEY AUTOINCREMENT(1,1) PRIMARY KEY;")
    gvarDatabase.Execute ("ALTER TABLE CLARK ADD COLUMN CLR_EMAIL TEXT(50);")
    gvarDatabase.Execute ("ALTER TABLE CLARK ADD COLUMN CLR_FAX TEXT(20);")
    
    gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN INS_KEY LONG")
    gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN BRA_KEY LONG")
    gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN CLR_KEY LONG")
    gvarDatabase.Execute ("ALTER TABLE ASSESSMENT ADD COLUMN ASO_KEY LONG")
    
    'Fix The INSURANCE TABLE
    lvarQuery1 = "SELECT ASS_KEY,ASS_INSURANCE, ASS_VAT_NO FROM ASSESSMENT"
    Set lvarRS1 = gvarDatabase.OpenRecordset(lvarQuery1, dbOpenForwardOnly)
    Do While Not lvarRS1.EOF
        lvarQuery2 = "SELECT INS_KEY FROM INSURANCE WHERE INS_NAME='" & lvarRS1("ASS_INSURANCE") & "'" & _
                     " AND INS_VAT_NO='" & lvarRS1("ASS_VAT_NO") & "'"
        lvarVal = CStr(gvarDatabase.OpenRecordset(lvarQuery2, dbOpenForwardOnly)("INS_KEY"))
        
        lvarQuery2 = "UPDATE ASSESSMENT SET INS_KEY = " & lvarVal & " WHERE ASS_KEY=" & lvarRS1("ASS_KEY")
        gvarDatabase.Execute lvarQuery2
        lvarRS1.MoveNext
    Loop
    'Add Extra Assessment Fields
    
    'Fix
    lvarQuery1 = "SELECT ASS_KEY, ASS_INSURANCE_BRANCH, ASS_VAT_NO FROM ASSESSMENT"
    Set lvarRS1 = gvarDatabase.OpenRecordset(lvarQuery1, dbOpenForwardOnly)
    Do While Not lvarRS1.EOF
        lvarQuery2 = "SELECT BRA_KEY FROM BRANCH WHERE BRA_NAME='" & lvarRS1("ASS_INSURANCE_BRANCH") & "'"

        lvarVal = CStr(gvarDatabase.OpenRecordset(lvarQuery2, dbOpenForwardOnly)("BRA_KEY"))
        lvarQuery2 = "UPDATE ASSESSMENT SET BRA_KEY = " & lvarVal & " WHERE ASS_KEY=" & lvarRS1("ASS_KEY")
        gvarDatabase.Execute lvarQuery2
        lvarRS1.MoveNext
    Loop
    'Add Extra Assessment Fields
    
    'Fix Insurance Clarks
    lvarQuery1 = "SELECT ASS_KEY, ASS_INSURANCE_CLARK, ASS_INSURANCE_TEL FROM ASSESSMENT"
    Set lvarRS1 = gvarDatabase.OpenRecordset(lvarQuery1, dbOpenForwardOnly)
    Do While Not lvarRS1.EOF
        lvarQuery2 = "SELECT CLR_KEY FROM CLARK WHERE CLR_NAME='" & lvarRS1("ASS_INSURANCE_CLARK") & "'" & _
                     " AND CLR_TEL='" & lvarRS1("ASS_INSURANCE_TEL") & "'"
        lvarVal = CStr(gvarDatabase.OpenRecordset(lvarQuery2, dbOpenForwardOnly)("CLR_KEY"))
        lvarQuery2 = "UPDATE ASSESSMENT SET CLR_KEY = " & lvarVal & " WHERE ASS_KEY=" & lvarRS1("ASS_KEY")
        gvarDatabase.Execute lvarQuery2
        lvarRS1.MoveNext
    Loop
    'Add Extra Assessment Fields
    
    'Fix Assesors
    lvarQuery1 = "SELECT ASS_KEY, ASS_ASSESSOR, ASS_ASSESSOR_TEL FROM ASSESSMENT"
    Set lvarRS1 = gvarDatabase.OpenRecordset(lvarQuery1, dbOpenForwardOnly)
    Do While Not lvarRS1.EOF
        lvarQuery2 = "SELECT ASO_KEY FROM ASSESOR WHERE ASO_NAME=""" & lvarRS1("ASS_ASSESSOR") & """" & _
                     " AND ASO_TEL=""" & lvarRS1("ASS_ASSESSOR_TEL") & """"
        lvarVal = CStr(gvarDatabase.OpenRecordset(lvarQuery2, dbOpenForwardOnly)("ASO_KEY"))
        lvarQuery2 = "UPDATE ASSESSMENT SET ASO_KEY = " & lvarVal & " WHERE ASS_KEY=" & lvarRS1("ASS_KEY")
        gvarDatabase.Execute lvarQuery2
        lvarRS1.MoveNext
    Loop
    'Add Extra Assessment Fields
    'commit the fix
    DBEngine.CommitTrans
    Exit Sub
errorhandler:
    MsgBox DBEngine.Errors(0).Description
    DBEngine.Rollback
    
    On Error GoTo 0
End Sub

